package com.liushuai.eas.common;

import com.liushuai.eas.pojo.Student;
import com.liushuai.eas.pojo.Teacher;
import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.VerticalAlignment;
import jxl.format.*;
import jxl.write.*;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.lang.Boolean;
import java.util.List;

public class ExcelUtil {

    public void exportToExcel(String fileName, String[] title, int[] columnWidth, List<?> list, HttpServletResponse response, String exportObject, Boolean isTemplate) {

        response.reset();//清空response
        response.setContentType("application/x-msdownload");//定义输出类型
        response.setHeader("Content-Disposition", "attachment; filename=" + fileName);//设定输出文件头

        String realPath = "E:/Tools/IDEA_Ultemate/IntelliJ_IDEA_2021-2-3/program/EAS/eas-back/src/main/resources/static/template/exportToExcel.xls";

        if (isTemplate){
            if ("student".equals(exportObject)){
                realPath = "E:/Tools/IDEA_Ultemate/IntelliJ_IDEA_2021-2-3/program/EAS/eas-back/src/main/resources/static/template/studentTemplate.xls";
            }else if ("teacher".equals(exportObject)){
                realPath = "E:/Tools/IDEA_Ultemate/IntelliJ_IDEA_2021-2-3/program/EAS/eas-back/src/main/resources/static/template/teacherTemplate.xls";
            }
        }

        try {
            ServletOutputStream os = response.getOutputStream();
            Workbook workbook = Workbook.getWorkbook(new File(realPath));
            WorkbookSettings workbookSettings = new WorkbookSettings();
            workbookSettings.setWriteAccess(null);
            WritableWorkbook wwb = Workbook.createWorkbook(os, workbook, workbookSettings);
            WritableSheet sheet = null;

            //直接下载模板
            if (isTemplate){
                wwb.write();
                wwb.close();
                os.flush();
                os.close();
                return;
            }

            //字体设置字体-像素-粗体-斜体-下划线-颜色
            WritableFont font = new WritableFont(WritableFont.ARIAL,9,WritableFont.NO_BOLD,false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK);//空格
            WritableFont font1 = new WritableFont(WritableFont.ARIAL,12,WritableFont.BOLD,false, UnderlineStyle.NO_UNDERLINE,Colour.BLACK);//大标题
            WritableFont font2 = new WritableFont(WritableFont.ARIAL, 10, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK);//小标题
            WritableFont font3 = new WritableFont(WritableFont.ARIAL, 10, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK);//值

            //=================数据====================
            //大标题格式
            WritableCellFormat cellFormat1 = new WritableCellFormat(font1);
            cellFormat1.setWrap(true);
            cellFormat1.setBackground(Colour.SKY_BLUE);
            cellFormat1.setAlignment(Alignment.CENTRE);
            cellFormat1.setVerticalAlignment(VerticalAlignment.CENTRE);
            cellFormat1.setBorder(Border.ALL, BorderLineStyle.THIN);
            //小标题格式
            WritableCellFormat cellFormat2 = new WritableCellFormat(font2);
            cellFormat2.setWrap(true);
            cellFormat2.setBackground(Colour.SKY_BLUE);
            cellFormat2.setAlignment(Alignment.CENTRE);
            cellFormat2.setVerticalAlignment(VerticalAlignment.CENTRE);
            cellFormat2.setBorder(Border.ALL, BorderLineStyle.THIN);
            //靠左格式
            WritableCellFormat cellFormat4 = new WritableCellFormat(font3);
            cellFormat4.setWrap(true);
            cellFormat4.setBackground(Colour.WHITE);
            cellFormat4.setVerticalAlignment(VerticalAlignment.CENTRE);
            cellFormat4.setBorder(Border.ALL, BorderLineStyle.THIN);
            //空格式
            WritableCellFormat cellFormat_2 = new WritableCellFormat(font);
            cellFormat_2.setAlignment(Alignment.LEFT);
            cellFormat_2.setVerticalAlignment(VerticalAlignment.CENTRE);
            cellFormat_2.setBackground(Colour.WHITE);
            cellFormat_2.setBorder(Border.ALL, BorderLineStyle.THIN);
            cellFormat_2.setWrap(true);

            //获取sheet页
            sheet = wwb.getSheet(0);

            //分别给每列设置不同的宽度
            for (int i = 0; i < title.length; i++) {
                sheet.setColumnView(i,columnWidth[i]);
            }

            //当前行数
            int rowCount = 1;
            //小标题
            for (int i = 0; i < title.length; i++) {
                Label label = new Label(i, 0, title[i], cellFormat2);
                sheet.addCell(label);
                //数据
                if (list!=null&&list.size()>0){
                    if ("student".equals(exportObject)){
                        this.exportStudent((List<Student>) list,cellFormat4,sheet,rowCount);
                    }else if ("teacher".equals(exportObject)){
                        this.exportTeacher((List<Teacher>) list,cellFormat4,sheet,rowCount);
                    }

                }
            }

            wwb.write();
            wwb.close();
            os.flush();
            os.close();
        }catch (Exception e){
            System.out.println(e.getMessage());
        }
    }

    //导出学生数据
    private void exportStudent(List<Student> list, WritableCellFormat cellFormat, WritableSheet sheet,int rowCount) throws WriteException {

        for (int i = 0; i < list.size(); i++) {
            Label label1 = new Label(0, rowCount+i, String.valueOf(list.get(i).getId()), cellFormat);
            Label label2 = new Label(1, rowCount+i, String.valueOf(list.get(i).getName()), cellFormat);
            Label label3 = new Label(2, rowCount+i, String.valueOf(list.get(i).getSex()), cellFormat);
            Label label4 = new Label(3, rowCount+i, String.valueOf(list.get(i).getClasses().getName()), cellFormat);
            Label label5 = new Label(4, rowCount+i, String.valueOf(list.get(i).getClasses().getMajor().getName()), cellFormat);
            Label label6 = new Label(5, rowCount+i, String.valueOf(list.get(i).getClasses().getMajor().getDepartment().getName()), cellFormat);
            Label label7 = new Label(6, rowCount+i, String.valueOf(list.get(i).getBirthday()), cellFormat);
            Label label8 = new Label(7, rowCount+i, String.valueOf(list.get(i).getJoinDate()), cellFormat);
            Label label9 = new Label(8, rowCount+i, String.valueOf(list.get(i).getEduSystem()), cellFormat);
            Label label10 = new Label(9, rowCount+i, String.valueOf(list.get(i).getIdentity()), cellFormat);

            sheet.addCell(label1);
            sheet.addCell(label2);
            sheet.addCell(label3);
            sheet.addCell(label4);
            sheet.addCell(label5);
            sheet.addCell(label6);
            sheet.addCell(label7);
            sheet.addCell(label8);
            sheet.addCell(label9);
            sheet.addCell(label10);
        }
    }

    //导出学生数据
    private void exportTeacher(List<Teacher> list, WritableCellFormat cellFormat, WritableSheet sheet,int rowCount) throws WriteException {

        for (int i = 0; i < list.size(); i++) {
            Label label1 = new Label(0, rowCount+i, String.valueOf(list.get(i).getId()), cellFormat);
            Label label2 = new Label(1, rowCount+i, String.valueOf(list.get(i).getName()), cellFormat);
            Label label3 = new Label(2, rowCount+i, String.valueOf(list.get(i).getSex()), cellFormat);
            Label label7 = new Label(3, rowCount+i, String.valueOf(list.get(i).getBirthday()), cellFormat);
            Label label8 = new Label(4, rowCount+i, String.valueOf(list.get(i).getJoinDate()), cellFormat);
            Label label10 = new Label(5, rowCount+i, String.valueOf(list.get(i).getIdentity()), cellFormat);

            sheet.addCell(label1);
            sheet.addCell(label2);
            sheet.addCell(label3);
            sheet.addCell(label7);
            sheet.addCell(label8);
            sheet.addCell(label10);
        }
    }

}
